/*
Navicat SQL Server Data Transfer

Source Server         : LOCALHOST
Source Server Version : 110000
Source Host           : .:1433
Source Database       : AAACHAM2
Source Schema         : dbo

Target Server Type    : SQL Server
Target Server Version : 110000
File Encoding         : 65001

Date: 2014-04-25 13:16:21
*/


-- ----------------------------
-- Table structure for tb0104
-- ----------------------------
DROP TABLE [dbo].[tb0104]
GO
CREATE TABLE [dbo].[tb0104] (
[VoucherID] varchar(20) NOT NULL ,
[TableID] varchar(20) NOT NULL DEFAULT ('tb0104') ,
[TranMonth] int NULL ,
[TranYear] int NULL ,
[DivisionID] varchar(20) NULL ,
[VoucherTypeID] varchar(20) NULL ,
[VoucherDate] datetime NULL ,
[VoucherNo] varchar(20) NULL ,
[ObjectID] varchar(20) NULL ,
[ProjectID] varchar(20) NULL ,
[OrderID] varchar(20) NULL ,
[BatchID] varchar(20) NULL ,
[WareHouseID] varchar(20) NULL ,
[ReDeTypeID] varchar(20) NULL ,
[KindVoucherID] int NULL ,
[WareHouseID2] varchar(20) NULL ,
[Status] tinyint NOT NULL DEFAULT ((0)) ,
[EmployeeID] varchar(20) NULL ,
[Description] nvarchar(250) NULL ,
[CreateDate] datetime NULL ,
[CreateUserID] varchar(50) NULL ,
[LastModifyUserID] varchar(50) NULL ,
[LastModifyDate] datetime NULL ,
[RefNo01] varchar(50) NULL ,
[RefNo02] varchar(50) NULL ,
[RDAddress] nvarchar(250) NULL ,
[ContactPerson] nvarchar(150) NULL ,
[VATObjectName] nvarchar(150) NULL ,
[ShipperID] varchar(20) NULL ,
[Transport] nvarchar(100) NULL ,
[Note01] nvarchar(200) NULL ,
[Note02] nvarchar(200) NULL ,
[Note03] nvarchar(200) NULL ,
[Note04] nvarchar(200) NULL ,
[ContractNo] varchar(50) NULL 
)


GO

-- ----------------------------
-- Indexes structure for table tb0104
-- ----------------------------
CREATE INDEX [IX_tb0104] ON [dbo].[tb0104]
([VoucherDate] ASC, [DivisionID] ASC) 
GO

-- ----------------------------
-- Primary Key structure for table tb0104
-- ----------------------------
ALTER TABLE [dbo].[tb0104] ADD PRIMARY KEY NONCLUSTERED ([VoucherID])
GO

-- ----------------------------
-- Checks structure for table tb0104
-- ----------------------------
ALTER TABLE [dbo].[tb0104] ADD CHECK (([VoucherID] IS NOT NULL AND [VoucherID]<>''))
GO

-- ----------------------------
-- Triggers structure for table tb0104
-- ----------------------------
--DROP TRIGGER [dbo].[AZ2006]
GO
CREATE TRIGGER [dbo].[AZ2006]
ON [dbo].[tb0104]
AFTER UPDATE
AS

Declare  @tb0104_cur as cursor,
	 @DivisionID as varchar(20),
	@WareHouseID_Old  as varchar(20),
	@WareHouseID_New  as varchar(20),
	@WareHouseID2_Old  as varchar(20),
	@WareHouseID2_New  as varchar(20),
	@KindVoucherID  as varchar(20),
	@VoucherID as varchar(20),
	@TranMonth int,
	@TranYear int,
	@tb0105_cur as cursor,
	@InventoryID as varchar(20),
	@DebitAccountID as varchar(20),
	@CreditAccountID as varchar(20),
	@Quantity as Decimal(28,8),
	@ConvertedAmount as Money,
	@MethodID as tinyint,
	@IsSource as tinyint,
	@IsLimitDate as tinyint


Set @tb0104_cur = Cursor Scroll KeySet For
	Select Ins.KindVoucherID,  Ins.VoucherID, Ins.DivisionID, Ins.TranMonth, Ins.TranYear, Del.WareHouseID, isnull(Del.WareHouseID2,''), Ins.WareHouseID, isnull(Ins.WareHouseID2,'')
	From  	inserted Ins 	inner join Deleted  Del on Ins.VoucherID = Del.VoucherID
				
	Where  (Del.WareHouseID <> Ins.WareHouseID) Or
		(isnull(Del.WareHouseID2,'')<> isnull(Ins.WareHouseID2,'') and Ins.KindVoucherID =3)

Open @tb0104_cur
Fetch Next From @tb0104_cur Into @KindVoucherID, @VoucherID, @DivisionID ,@TranMonth, @TranYear,  @WareHouseID_Old, @WareHouseID2_Old, @WareHouseID_New, @WareHouseID2_New
While @@FETCH_STATUS = 0
Begin	
	
	Set @tb0105_cur = Cursor Scroll KeySet For
	      	Select tb0105.InventoryID, ActualQuantity, ConvertedAmount, DebitAccountID, CreditAccountID, MethodID, IsSource, IsLimitDate
		From tb0105 inner join tb0054 on tb0054.InventoryID = tb0105.InventoryID
		Where tb0105.DivisionID = @DivisionID and TranMonth = @TranMonth and TranYear = @TranYear and VoucherID = @VoucherID

	Open @tb0105_cur
	Fetch Next From @tb0105_cur Into @InventoryID, @Quantity, @ConvertedAmount, @DebitAccountID, @CreditAccountID , @MethodID, @IsSource, @IsLimitDate
	If @KindVoucherID in (1,5,7,9)   --- Nhap kho 
		If @MethodID in (1, 2, 3) Or @IsSource<>0 or @IsLimitDate <>0
			Update tb0007 set WareHouseID =@WareHouseID_New
			Where 	ReVoucherID =@VoucherID and
				InventoryID = @InventoryID

		Begin			
		        While @@FETCH_STATUS = 0	          	
			Begin
				Exec pr0054 @DivisionID, @WareHouseID_Old, @TranMonth, @TranYear, @InventoryID, @ConvertedAmount, @Quantity, @DebitAccountID , @CreditAccountID, 1 -- (1) la cap nhat tang,(0 ) la cap nhat giam
				Exec pr0053 @DivisionID, @WareHouseID_New, @TranMonth, @TranYear, @InventoryID, @ConvertedAmount, @Quantity, @DebitAccountID , @CreditAccountID, 1 -- (1) la cap nhat tang,(0 ) la cap nhat giam
				Fetch Next From @tb0105_cur Into @InventoryID, @Quantity, @ConvertedAmount, @DebitAccountID, @CreditAccountID , @MethodID, @IsSource, @IsLimitDate
			End

		End
	If @KindVoucherID in (2,4,6,8)  --- Xuat kho
		Begin
		  While @@FETCH_STATUS = 0	  
			Begin
		  		Exec pr0054 @DivisionID, @WareHouseID_Old, @TranMonth, @TranYear, @InventoryID, @ConvertedAmount, @Quantity, @DebitAccountID , @CreditAccountID, 0 -- (1) la cap nhat tang,(0 ) la cap nhat giam
				Exec pr0053 @DivisionID, @WareHouseID_New, @TranMonth, @TranYear, @InventoryID, @ConvertedAmount, @Quantity, @DebitAccountID , @CreditAccountID, 0 -- (1) la cap nhat tang,(0 ) la cap nhat giam
				Fetch Next From @tb0105_cur Into @InventoryID, @Quantity, @ConvertedAmount, @DebitAccountID, @CreditAccountID , @MethodID, @IsSource, @IsLimitDate
			End

		End
	Else
	If @KindVoucherID =3  --- Van chuyen noi bo
		Begin
		  While @@FETCH_STATUS = 0	  
			Begin
		          	IF @WareHouseID_Old<>@WareHouseID_New  -- KHo nhap
			   Begin	
				Exec pr0054 @DivisionID, @WareHouseID_Old, @TranMonth, @TranYear, @InventoryID, @ConvertedAmount, @Quantity, @DebitAccountID , @CreditAccountID, 1 -- (1) la cap nhat tang,(0 ) la cap nhat giam
				Exec pr0053 @DivisionID, @WareHouseID_New , @TranMonth, @TranYear, @InventoryID, @ConvertedAmount, @Quantity, @DebitAccountID , @CreditAccountID, 1 -- (1) la cap nhat tang,(0 ) la cap nhat giam		
			   End
			IF @WareHouseID2_Old<>@WareHouseID2_New  -- KHo Xuat
			  Begin
				Exec pr0054 @DivisionID, @WareHouseID2_Old, @TranMonth, @TranYear, @InventoryID, @ConvertedAmount, @Quantity, @DebitAccountID , @CreditAccountID, 0 -- (1) la cap nhat tang,(0 ) la cap nhat giam
				Exec pr0053 @DivisionID, @WareHouseID2_New , @TranMonth, @TranYear, @InventoryID, @ConvertedAmount, @Quantity, @DebitAccountID , @CreditAccountID, 0 -- (1) la cap nhat tang,(0 ) la cap nhat gia				
			  End	
			Fetch Next From @tb0105_cur Into @InventoryID, @Quantity, @ConvertedAmount, @DebitAccountID, @CreditAccountID , @MethodID, @IsSource, @IsLimitDate
			End	

		End
	
		Close @tb0105_cur
	
--Print @WareHouseID_Old + ' NEW   : ' +@WareHouseID_New
Fetch Next From @tb0104_cur Into @KindVoucherID, @VoucherID, @DivisionID ,@TranMonth, @TranYear,  @WareHouseID_Old, @WareHouseID2_Old, @WareHouseID_New, @WareHouseID2_New
End
	
Close @tb0104_cur



GO
